package com.jyh.homework2;

import com.jyh.utils.JDBCUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @Author JYH
 * @Date 2024/8/22 20:20
 * @Description
 */
public class Emp {
    @Test
    public void test1() throws SQLException {
        //编写一个java方法，在键盘上输入员工信息，将题中三条数据插入到数据库表中
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="INSERT INTO emp VALUE(1,'张三',18,90,'武汉'),(2,'李四',28,50,'上海'),(3,'李五六',29,50,'北京')";
        int count = statement.executeUpdate(sql);
        System.out.println(count==3?"添加成功!":"添加失败!");
        statement.close();
        connection.close();
    }

    @Test
    public void test2() throws SQLException {
        //编写一个java方法，按照地址统计，统计不同省份的员工数量，并打印输出
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="SELECT emp.address, COUNT(*) FROM emp GROUP BY emp.address";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            String address = rs.getString("address");
            int count = rs.getInt(2);
            System.out.println(address+"--"+count);
        }
        rs.close();
        statement.close();
        connection.close();
    }

    @Test
    public void test3() throws SQLException {
        //编写一个java方法，查询出姓‘李’的并且名字为三个字的的来自北京的员工信息，并打印输出
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="SELECT * FROM emp WHERE e_name LIKE('李__') AND address='北京'";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            int id = rs.getInt("id");
            String e_name = rs.getString("e_name");
            int e_age = rs.getInt("e_age");
            int weight = rs.getInt("weight");
            String address = rs.getString("address");
            System.out.println(id+"--"+e_name+"--"+e_age+"--"+weight+"--"+address);
        }
        rs.close();
        statement.close();
        connection.close();
    }

    @Test
    public void test4() throws SQLException {
        //编写一个java方法，查询出3年后年龄超过30岁的员工信息，并且按照年龄从大到小排序，并打印输出
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="SELECT * FROM emp WHERE (e_age+3)>30 ORDER BY e_age DESC";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            int id = rs.getInt("id");
            String e_name = rs.getString("e_name");
            int e_age = rs.getInt("e_age");
            int weight = rs.getInt("weight");
            String address = rs.getString("address");
            System.out.println(id+"--"+e_name+"--"+e_age+"--"+weight+"--"+address);
        }
        rs.close();
        statement.close();
        connection.close();
    }
}
